package top.braycep.db;

import cn.hutool.core.util.BooleanUtil;
import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.SQLiteStyle;
import org.beetl.sql.ext.DebugInterceptor;
import top.braycep.bean.JarFile;
import top.braycep.bean.JarInnerFile;
import top.braycep.bean.Setting;
import top.braycep.bean.Task;
import top.braycep.log.LogUtils;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Set;

public class DbUtils {
    private static final Db db;
    private static final SQLManager sqlManager;

    public static final String TABLE_FILES;
    public static final String TABLE_JARFILES;
    public static final String TABLE_SETTING;
    public static final String TABLE_TASKS;

    static {
        db = Db.use();

        TABLE_FILES = "table_files";
        TABLE_JARFILES = "table_jarfiles";
        TABLE_SETTING = "table_setting";
        TABLE_TASKS = "table_tasks";

        DataSource ds = DbUtil.getDs();
        ConnectionSource connectionSource = ConnectionSourceHelper.getSingle(ds);

        SQLManagerBuilder builder = new SQLManagerBuilder(connectionSource);
        builder.setNc(new UnderlinedNameConversion());
        builder.setInters(new Interceptor[]{new DebugInterceptor()});
        builder.setDbStyle(new SQLiteStyle());

        sqlManager = builder.build();

        try {
            db.find(Entity.create(TABLE_JARFILES));
        } catch (SQLException e) {
            if (e.getMessage().contains("no such table")) {
                try {
                    LogUtils.showLog("初始化数据库表[" + TABLE_JARFILES + "]");
                    db.execute("create table " + TABLE_JARFILES + "\n" +
                            "(\n" +
                            "\tid integer not null\n" +
                            "\t\tconstraint tbl_file_pk\n" +
                            "\t\t\tprimary key autoincrement,\n" +
                            "\tfilename text not null,\n" +
                            "\tfilepath text,\n" +
                            "\tisDefault integer default 0 not null\n" +
                            ");\n" +
                            "\n" +
                            "create unique index tbl_file_id_uindex\n" +
                            "\ton table_jarfiles (id);\n" +
                            "\n");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        try {
            db.find(Entity.create(TABLE_FILES));
        } catch (SQLException e) {
            if (e.getMessage().contains("no such table")) {
                try {
                    LogUtils.showLog("初始化数据库表[" + TABLE_FILES + "]");
                    db.execute("create table " + TABLE_FILES + "\n" +
                            "(\n" +
                            "\tid integer not null\n" +
                            "\t\tconstraint table_files_pk\n" +
                            "\t\t\tprimary key autoincrement,\n" +
                            "\trealpath text not null,\n" +
                            "\tmappedpath text not null,\n" +
                            "\tjar_id integer not null\n" +
                            "\t\treferences table_jarfiles,\n" +
                            "\trule text\n" +
                            ");\n" +
                            "\n" +
                            "create unique index table_files_id_uindex\n" +
                            "\ton table_files (id);\n" +
                            "\n");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        try {
            db.find(Entity.create(TABLE_SETTING));
        } catch (SQLException e) {
            if (e.getMessage().contains("no such table")) {
                try {
                    LogUtils.showLog("初始化数据库表[" + TABLE_SETTING + "]");
                    db.execute("create table " + TABLE_SETTING + "\n" +
                            "(\n" +
                            "\tid integer not null\n" +
                            "\t\tconstraint table_setting_pk\n" +
                            "\t\t\tprimary key autoincrement,\n" +
                            "\tname text not null,\n" +
                            "\tvalue text,\n" +
                            "\tdesc text\n" +
                            ");\n" +
                            "\n" +
                            "create unique index table_setting_id_uindex\n" +
                            "\ton table_setting (id);\n" +
                            "\n");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        try {
            db.find(Entity.create(TABLE_TASKS));
        } catch (SQLException e) {
            if (e.getMessage().contains("no such table")) {
                try {
                    LogUtils.showLog("初始化数据库表[" + TABLE_TASKS + "]");
                    db.execute("create table " + TABLE_TASKS + "\n" +
                            "(\n" +
                            "\tid integer not null\n" +
                            "\t\tconstraint table_tasks_pk\n" +
                            "\t\t\tprimary key autoincrement,\n" +
                            "\tgrp text not null,\n" +
                            "\tjar_id int not null,\n" +
                            "\tord integer default 0 not null\n" +
                            ");\n" +
                            "\n" +
                            "create unique index table_tasks_id_uindex\n" +
                            "\ton table_tasks (id);\n" +
                            "\n");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    public static JarFile findConfigById(Long id) {
        LogUtils.showLog("查询Jar配置信息[" + id + "]");
        return sqlManager.unique(JarFile.class, id);
    }

    public static List<JarFile> findAllConfig() {
        LogUtils.showLog("查询所有Jar配置信息");
        return sqlManager.all(JarFile.class);
    }

    public static String getDefaultSavePath() {
        LogUtils.showLog("查询Jar默认保存位置");
        List<Setting> setting = sqlManager.execute(new SQLReady("select * from " + TABLE_SETTING + " where name = ?", "defaultSavePath"), Setting.class);
        return setting.size() == 0 ? "" : setting.get(0).getValue();
    }

    public static void saveJarFile(JarFile jarFile) {
        LogUtils.showLog("保存Jar文件配置：" + jarFile.getFilename());
        sqlManager.upsert(jarFile);
        for (JarInnerFile innerFile : jarFile.getInnerFiles()) {
            innerFile.setJarId(jarFile.getId());
            sqlManager.upsert(innerFile);
        }
    }

    public static JarFile findDefault() {
        LogUtils.showLog("查询默认Jar配置信息");
        List<JarFile> jarFiles = sqlManager.execute(new SQLReady("select * from " + TABLE_JARFILES + " where isDefault = 1"), JarFile.class);
        if (jarFiles.size() > 0) {
            return jarFiles.get(0);
        }
        return null;
    }

    public static void setDefaultConfig(Long id) {
        if (id != null) {
            LogUtils.showLog("设置默认Jar配置信息[" + id + "]");
            sqlManager.executeUpdate(new SQLReady("update " + TABLE_JARFILES + " set isDefault = 0"));
            sqlManager.executeUpdate(new SQLReady("update " + TABLE_JARFILES + " set isDefault = 1 where id = ?", id));
        }
    }

    public static void delConfigs(List<Long> jarIds) {
        LogUtils.showLog("删除选中Jar配置" + JSONUtil.toJsonStr(jarIds));
        for (Long jarId : jarIds) {
            sqlManager.executeUpdate(new SQLReady("delete from " + TABLE_FILES + " where jar_id = ?", jarId));
            sqlManager.deleteById(JarFile.class, jarId);
        }
    }

    public static List<Task> findAllTask() {
        return sqlManager.all(Task.class);
    }

    public static List<Setting> findAllSettings() {
        LogUtils.showLog("加载设置信息");
        return sqlManager.all(Setting.class);
    }

    public static void updateSetting(Setting setting) {
        if (setting != null && setting.getId() != null) {
            LogUtils.showLog("更新设置信息: " + JSONUtil.toJsonStr(setting));
            sqlManager.executeUpdate(new SQLReady("update " + TABLE_SETTING + " set value = ? where id = ?", setting.getValue(), setting.getId()));
        }
    }

    public static boolean isDeleteOldJar() {
        List<Setting> settings = sqlManager.execute(new SQLReady("select * from " + TABLE_SETTING + " where name = ?", "deleteOldJarFile"), Setting.class);
        if (settings != null && settings.size() > 0) {
            return "1".equals(settings.get(0).getValue());
        }
        return false;
    }
}
